package com.rabbit.eap.framework.utils.excel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;

/**
 * @projectName eap
 * @packageName com.rabbit.eap.common.excel
 * @className ExcelUtils
 * @author： rabbit
 * @version： 1.0
 * @since： 2020/4/9 23:52
 * <p>Description: ExcelUtils </p>
 */
public class ExcelUtils {
    private static String title = "";
    private static String msg = "";

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
                                   String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
                                      HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            //throw new NormalException(e.getMessage());
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            //throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            //throw new NormalException(e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            //throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }

    public static LocalDateTime dateConvertLocalDateTime(Date date) {
        if (date == null) {
            return null;
        }
        return LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
    }

    public static Date LocalDateTimeConvertDate(LocalDateTime localDateTime) {
        if (localDateTime == null) {
            return null;
        }
        return Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());
    }

    public static String getWebRootPath(String filePath) {
        try {
            String path = PoiPublicUtil.class.getClassLoader().getResource("").toURI().getPath();
            path = path.replace("WEB-INF/classes/", "");
            path = path.replace("file:/", "");
            return path + filePath;
        } catch (URISyntaxException e) {
            throw new RuntimeException(e);
        }
    }

    public static void writeExcel(String filePath, ExcelImportResult result, Class pojoClass, ExportParams exportParams) throws IOException {
        if (exportParams == null) {
            exportParams = new ExportParams();
        }
        FileOutputStream fos = new FileOutputStream(filePath);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, result.getList());
        workbook.write(fos);
        fos.close();
    }


    /**
     * @param @param templates  Excel列标题、描述、是否必填
     * @param @param downData  下拉框数据(数组)
     * @param @param downRows  下拉列的序号(数组,序号从0开始)
     * @return void
     * @throws
     * @Title: createExcelTemplate
     * @Description: 生成Excel导入模板
     */
    public static XSSFWorkbook createExcelTemplate(List<ExcelTemplate> templates,
                                                   String memo,
                                                   List<String[]> downData, String[] downRows,
                                                   List<String> archiveCategoryNameList,
                                                   Map<String, List<String>> typeMap,
                                                   String msgTitle, String message) {
        if (templates == null) {
            return null;
        }
        title = msgTitle;
        msg = message;
        XSSFWorkbook wb = new XSSFWorkbook();//创建工作薄

        XSSFSheet sheet = getSheet(wb, memo, templates);

        XSSFSheet sheetDropDown = wb.createSheet("SheetDropDown");

        // 隐藏
        wb.setSheetHidden(wb.getSheetIndex(sheetDropDown), true);
        if (archiveCategoryNameList != null && typeMap != null) {
            setCascadeDropDown(wb, sheet, archiveCategoryNameList, typeMap, sheetDropDown);
        }
        if (downData != null && downRows != null) {
            setDropDown(wb, sheet, downData, downRows);
        }

        return wb;
    }

    private static XSSFSheet getSheet(XSSFWorkbook wb, String memo,
                                      List<ExcelTemplate> templates) {

        // 必填表头样式
        XSSFCellStyle style = wb.createCellStyle();
        //字体样式
        XSSFFont fontStyle = wb.createFont();
        setStyle(style, fontStyle, false);

        // 必填表头样式
        XSSFCellStyle styleRequired = wb.createCellStyle();
        //字体样式
        XSSFFont fontStyleRequired = wb.createFont();
        setStyle(styleRequired, fontStyleRequired, true);

        //新建sheet
        XSSFSheet sheet = wb.createSheet("Sheet");

        // 生成sheet1内容
        // 描述说明列
        XSSFRow memoRow = sheet.createRow(0);

        XSSFCell memoCell = memoRow.createCell(0);

        // 自动换行 左对齐 垂直居中
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        memoCell.setCellStyle(cellStyle);

        memoRow.setHeight((short) 2600);
        memoCell.setCellValue(memo);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
        sheet.addMergedRegion(region);

        //第一个sheet的第二行为标题
        XSSFRow rowFirst = sheet.createRow(1);
        rowFirst.setHeight((short) 800);
        //第一个sheet的第三行为对应的数据库字段
        XSSFRow rowHeader = sheet.createRow(2);
        //写入数据
        for (int i = 0; i < templates.size(); i++) {
            ExcelTemplate excelTemplate = templates.get(i);
            //获取第一行的每个单元格
            XSSFCell cell = rowFirst.createCell(i);
            int cellWidth = 10000;
            /*if (i < 4) {
                cellWidth = 15000;
            }*/
            //设置每列的列宽
            sheet.setColumnWidth(i, cellWidth);
            if (excelTemplate.isRequired()) {
                //加必填样式
                cell.setCellStyle(styleRequired);
            } else {
                //加样式
                cell.setCellStyle(style);
            }
            //往描述单元格里写数据
            cell.setCellValue(excelTemplate.getDescription());
            //获取第一行的每个单元格
            XSSFCell cellHeader = rowHeader.createCell(i);
            //往列key单元格里写数据
            cellHeader.setCellValue(excelTemplate.getHeader());
        }
        return sheet;
    }

    private static void setDropDown(XSSFWorkbook wb, XSSFSheet sheet, List<String[]> downData, String[] downRows) {
        int index = 0;
        XSSFRow row = null;
        for (int r = 0; r < downRows.length; r++) {
            XSSFSheet sheetDropDown = wb.createSheet("SheetDropDown" + r);

            // 隐藏
            wb.setSheetHidden(wb.getSheetIndex(sheetDropDown), true);

            String[] dlData = downData.get(r);//获取下拉对象
            int length = 0;
            for (String down : dlData) {
                length += down.length();
            }
            int rownum = Integer.parseInt(downRows[r]);

            if (length < 255) { //255个字符以内的下拉
                //255以内的下拉,参数分别是：作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
                sheet.addValidationData(setDataValidation(sheet, dlData, 3,
                        1000, rownum, rownum)); //超过255个报错
            } else { //255个字符以上的下拉，即下拉列表元素很多的情况
                setMaxDropDown(sheetDropDown, sheet, index, row, dlData, 3,
                        1000, rownum, rownum);

//                index++;
            }
        }
    }

    private static XSSFCellStyle setStyle(XSSFCellStyle style, XSSFFont fontStyle, boolean isRequired) {
        if (style == null || fontStyle == null) {
            return null;
        }
        //表头样式
        // 创建一个居中格式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        //字体样式
        fontStyle.setFontHeightInPoints((short) 12);
        fontStyle.setBold(true);
        if (isRequired) {
            fontStyle.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        }
        style.setFont(fontStyle);
        return style;
    }

    /**
     * @param @param  strFormula
     * @param @param  firstRow   起始行
     * @param @param  endRow     终止行
     * @param @param  firstCol   起始列
     * @param @param  endCol     终止列
     * @param @return
     * @return DataValidation
     * @throws
     * @Title: setDataValidation
     * @Description: 下拉列表元素很多的情况 (255以上的下拉)
     */
    private static DataValidation setDataValidation(XSSFSheet sheet, String strFormula, int firstRow,
                                                    int endRow, int firstCol, int endCol) {

        XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidationHelper help = new XSSFDataValidationHelper(sheet);
        DataValidation validation = help.createValidation(constraint, regions);
        // 设置输入信息提示信息
//        validation.createPromptBox(title, msg);
        // 设置输入错误提示信息
//        validation.createErrorBox(title, msg);
        return validation;
    }

    /**
     * @param sheetDropDown: 存放下拉框数据的 sheet
     * @param sheet:         主表sheet
     * @param index:
     * @param row:           行
     * @param dlData:        下拉框数据 String[]
     * @param firstRow:      显示在sheetDropDown开始行
     * @param endRow:        显示在sheetDropDown结束行
     * @param firstCol:      显示在sheetDropDown开始列
     * @param endCol:        显示在sheetDropDown结束列, 一般和firstCol相同
     * @Description 级联下拉列表元素很多的情况 (255以上的下拉)
     * @methoName: setMaxDropDown
     * @return: void
     * @throws:
     */
    private static void setMaxDropDown(XSSFSheet sheetDropDown, XSSFSheet sheet,
                                       int index, XSSFRow row, String[] dlData,
                                       int firstRow, int endRow, int firstCol,
                                       int endCol) {
        //设置下拉框数据
        String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        //1、设置有效性
        //String strFormula = "sheetDropDown!$A$1:$A$5000" ; //sheetDropDown第A1到A5000作为下拉列表来源数据
        String strFormula =
                sheetDropDown.getSheetName() + "!$" + arr[index] + "$1:$" + arr[index] +
                        "$5000"; //sheetDropDown第A1到A5000作为下拉列表来源数据
        sheetDropDown.setColumnWidth(index, 4000); //设置每列的列宽
        //设置数据有效性加载在哪个单元格上,参数分别是：从sheetDropDown获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
        sheet.addValidationData(setDataValidation(sheet, strFormula, firstRow
                , endRow, firstCol, endCol));
        //下拉列表元素很多的情况

        //2、生成sheetDropDown内容
        for (int j = 0; j < dlData.length; j++) {
            if (index == 0) { //第1个下拉选项，直接创建行、列
                row = sheetDropDown.createRow(j); //创建数据行
                sheetDropDown.setColumnWidth(index, 4000); //设置每列的列宽
                row.createCell(0).setCellValue(dlData[j]); //设置对应单元格的值

            } else { //非第1个下拉选项

                int rowCount = sheetDropDown.getLastRowNum();
                // System.out.println("========== LastRowNum =========" + rowCount);
                if (j <= rowCount) { //前面创建过的行，直接获取行，创建列
                    //获取行，创建列
                    sheetDropDown.getRow(j).createCell(index).setCellValue(dlData[j]); //设置对应单元格的值

                } else { //未创建过的行，直接创建行、创建列
                    sheetDropDown.setColumnWidth(j, 4000); //设置每列的列宽
                    //创建行、创建列
                    sheetDropDown.createRow(j).createCell(index).setCellValue(dlData[j]); //设置对应单元格的值
                }
            }
        }
    }


    /**
     * @param @param  sheet
     * @param @param  textList
     * @param @param  firstRow
     * @param @param  endRow
     * @param @param  firstCol
     * @param @param  endCol
     * @param @return
     * @return DataValidation
     * @throws
     * @Title: setDataValidation
     * @Description: 下拉列表元素不多的情况(255以内的下拉)
     */
    private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

        DataValidationHelper helper = sheet.getDataValidationHelper();
        //加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        //DVConstraint constraint = new DVConstraint();
        constraint.setExplicitListValues(textList);

        //设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);

        //数据有效性对象

        return helper.createValidation(constraint, regions);
    }

    private static void setCascadeDropDown(XSSFWorkbook book, XSSFSheet sheet, List<String> archiveCategoryNameList,
                                           Map<String, List<String>> typeMap,
                                           XSSFSheet sheetDropDown) {
        // 创建一个专门用来存放地区信息的隐藏sheet页
        XSSFSheet hideSheet = book.createSheet("hide_sheet");
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        book.setSheetHidden(book.getSheetIndex(hideSheet), true);

        int rowId = 0;
        // 设置第一行，存文档分类的信息
        XSSFRow provinceRow = hideSheet.createRow(rowId++);
        XSSFRow row1 = sheetDropDown.createRow(0);
        provinceRow.createCell(0).setCellValue("aaa");
        for (int i = 0; i < archiveCategoryNameList.size(); i++) {
            Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(archiveCategoryNameList.get(i));
            row1.createCell(i).setCellValue(archiveCategoryNameList.get(i));
        }
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = typeMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = typeMap.get(key);

            XSSFRow row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }

            // 添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = book.createName();
            name.setNameName(key);
            String formula = "hide_sheet!" + range;
            name.setRefersToFormula(formula);
        }

        XSSFRow row = null;
//        sheet.addValidationData(setDataValidation(sheet, archiveCategoryNameList.toArray(new String[]{}), 2, 200, 0, 0));
//        firstRow 从第四行开始设置下拉框
        setMaxDropDown(sheetDropDown, sheet, 0, row,
                archiveCategoryNameList.toArray(new String[]{}), 3,
                1000, 0, 0);

        //对前1000行设置有效性, 从第四行开始设置下拉框
        for (int i = 4; i < 1000; i++) {
            setDataValidation("A", sheet, i, 2);
            setDataValidation("B", sheet, i, 3);
        }
    }

    /**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    public static void setDataValidation(String offset, XSSFSheet sheet, int rowNum, int colNum) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidation data_validation_list;
        data_validation_list = getDataValidationByFormula(
                "INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
        sheet.addValidationData(data_validation_list);
    }

    /**
     * 加载下拉列表内容
     *
     * @param formulaString
     * @param naturalRowIndex
     * @param naturalColumnIndex
     * @param dvHelper
     * @return
     */
    private static DataValidation getDataValidationByFormula(
            String formulaString, int naturalRowIndex, int naturalColumnIndex, XSSFDataValidationHelper dvHelper) {
        // 加载下拉列表内容
        // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
        //如果A2是江苏省，那么此处就是江苏省下的市信息。
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        int firstRow = naturalRowIndex - 1;
        int lastRow = naturalRowIndex - 1;
        int firstCol = naturalColumnIndex - 1;
        int lastCol = naturalColumnIndex - 1;
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                lastRow, firstCol, lastCol);
        // 数据有效性对象
        // 绑定
        XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        data_validation_list.setEmptyCellAllowed(false);
        data_validation_list.setSuppressDropDownArrow(true);
        data_validation_list.setShowErrorBox(true);
        // 设置输入信息提示信息
        data_validation_list.createPromptBox(title, msg);
        // 设置输入错误提示信息
        data_validation_list.createErrorBox(title, msg);
        return data_validation_list;
    }

    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public static String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }
}
